package com.hellogood.yaohuo.server.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Set;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.ConnectionPool;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.server.mgr.factory.MgrFactory;
import com.hellogood.yaohuo.shared.data.CallDTO;
import com.hellogood.yaohuo.shared.entity.PsDetail;
import com.hellogood.yaohuo.shared.entity.PsForm;
import com.hellogood.yaohuo.shared.exception.DatabaseException;
import com.hellogood.yaohuo.shared.vo.MainFrame;

public class PsFormDAO extends DBHelper {

	Logger log = Logger.getLogger(this.getClass());

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {
		List<PsForm> psFormList = new ArrayList<PsForm>();
		if (rs != null) {
			while (rs.next()) {
				PsForm psForm = new PsForm();
				psForm.setPsBillNo(rs.getString("PsBillNo"));
				String psDate = rs.getString("Psdate");
				if (psDate == null) {
					psDate = "";
				}
				psForm.setPsDate(psDate);
				String orgCode = rs.getString("OrgCode");
				psForm.setOrgCode(orgCode);
				String shopCode = rs.getString("ShopCode");
				psForm.setShopCode(shopCode);
				psForm.setCounts(rs.getInt("Counts"));
				psForm.setCost(rs.getBigDecimal("Cost"));
				psForm.setNetCost(rs.getBigDecimal("NetCost"));
				// psForm.setPsPrice(rs.getFloat("PsPrice"));
				psForm.setPsCost(rs.getBigDecimal("psCost"));
				psForm.setTotal(rs.getBigDecimal("Total"));
				String staffCode = rs.getString("StaffCode");
				psForm.setStaffCode(staffCode);
				String staffName = rs.getString("StaffName");
				psForm.setStaffName(staffName);
				psForm.setTimeMark(rs.getString("TimeMark"));
				String rzDate = rs.getString("RzDate");
				psForm.setRzDate(rzDate);
				String remark = rs.getString("Remark");
				psForm.setRemark(remark);
				psFormList.add(psForm);
			}
		}
		return psFormList;
	}

	public String getLastPsBillNo(String billNo) {
		String sql = "SELECT top 1 psbillno FROM PsForm WHERE psbillno like '" + billNo + "%' order by psbillno desc";
		return super.getLastBillNo(sql, "psBillNo");
	}

	public String createPeisong(PsForm form, boolean isModify) {
		String result = "";
		List<String> sqls = new ArrayList<String>();
		if (isModify) {
			String sql = "DELETE FROM psForm WHERE PsBillNo = '" + form.getPsBillNo() + "'";
			sqls.add(sql);
			sql = "DELETE FROM psDetail WHERE PsBillNo = '" + form.getPsBillNo() + "'";
			sqls.add(sql);
		}

		String sql = "INSERT INTO PsForm(psBillNo,psDate,orgCode,shopCode,counts,cost,netCost,total,staffCode,"
				+ "staffName,rzDate,remark,createTime,psCost) VALUES('"
				+ form.getPsBillNo()
				+ "',CONVERT(varchar(100), GETDATE(), 23),'"
				+ form.getOrgCode()
				+ "','"
				+ form.getShopCode()
				+ "',"
				+ form.getCounts()
				+ ","
				+ form.getCost()
				+ ","
				+ form.getCost()
				+ ","
				+ form.getTotal()
				+ ",'"
				+ form.getStaffCode()
				+ "','"
				+ form.getStaffName() + "','" + form.getRzDate() + "','" + form.getRemark() + "',getdate()," + form.getPsCost() + ")";
		sqls.add(sql);

		List<PsDetail> list = form.getList();
		if (list != null && (!list.isEmpty())) {
			for (PsDetail d : list) {
				sql = "INSERT INTO PsDetail(psBillNo,pluCode,pluName,barCode,bra,color,CSize,sglCount,jPrice,netJPrice,"
						+ "psPrice,price,counts,cost,netCost,total,rzDate,remark,OrgCode,createTime,depCode,depName,psCost) VALUES('"
						+ d.getPsBillNo()
						+ "','"
						+ d.getPluCode()
						+ "','"
						+ d.getPluName()
						+ "','"
						+ d.getBarCode()
						+ "','"
						+ d.getBra()
						+ "','"
						+ d.getColor()
						+ "','"
						+ d.getSize()
						+ "',"
						+ d.getSglCount()
						+ ","
						+ d.getjPrice()
						+ ","
						+ d.getNetJPrice()
						+ ","
						+ d.getPsPrice()
						+ ","
						+ d.getPrice()
						+ ","
						+ d.getCounts()
						+ ","
						+ d.getCost()
						+ ","
						+ d.getNetCost()
						+ ","
						+ d.getTotal()
						+ ",'"
						+ d.getRzDate()
						+ "','"
						+ d.getRemark()
						+ "','"
						+ d.getOrgCode()
						+ "',getDate(),'"
						+ d.getDepCode()
						+ "','"
						+ d.getDepName() + "'," + d.getPsCost() + ")";
				sqls.add(sql);
			}
		}

		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}

		return result;
	}

	public String createPeisongBatch(List<PsForm> formList, Set<String> billNos) {
		String result = "";
		List<String> sqls = new ArrayList<String>();

		for (PsForm form : formList) {
			String sql = "INSERT INTO PsForm(psBillNo,psDate,orgCode,shopCode,counts,cost,netCost,total,staffCode,"
					+ "staffName,rzDate,remark,createTime,psCost) VALUES('"
					+ form.getPsBillNo()
					+ "',CONVERT(varchar(100), GETDATE(), 23),'"
					+ form.getOrgCode()
					+ "','"
					+ form.getShopCode()
					+ "',"
					+ form.getCounts()
					+ ","
					+ form.getCost()
					+ ","
					+ form.getCost()
					+ ","
					+ form.getTotal()
					+ ",'"
					+ form.getStaffCode()
					+ "','"
					+ form.getStaffName() + "','" + form.getRzDate() + "','" + form.getRemark() + "',getdate()," + form.getPsCost() + ")";
			sqls.add(sql);

			List<PsDetail> list = form.getList();
			if (list != null && (!list.isEmpty())) {
				for (PsDetail d : list) {
					sql = "INSERT INTO PsDetail(psBillNo,pluCode,pluName,barCode,bra,color,CSize,sglCount,jPrice,netJPrice,"
							+ "psPrice,price,counts,cost,netCost,total,rzDate,remark,OrgCode,psCost,depCode,depName,createTime) VALUES('"
							+ d.getPsBillNo()
							+ "','"
							+ d.getPluCode()
							+ "','"
							+ d.getPluName()
							+ "','"
							+ d.getBarCode()
							+ "','"
							+ d.getBra()
							+ "','"
							+ d.getColor()
							+ "','"
							+ d.getSize()
							+ "',"
							+ d.getSglCount()
							+ ","
							+ d.getjPrice()
							+ ","
							+ d.getNetJPrice()
							+ ","
							+ d.getPsPrice()
							+ ","
							+ d.getPrice()
							+ ","
							+ d.getCounts()
							+ ","
							+ d.getCost()
							+ ","
							+ d.getNetCost()
							+ ","
							+ d.getTotal()
							+ ",'"
							+ d.getRzDate()
							+ "','"
							+ d.getRemark()
							+ "','"
							+ d.getOrgCode()
							+ "',"
							+ d.getPsCost()
							+ ",'"
							+ d.getDepCode() + "','" + d.getDepName() + "',getDate())";
					sqls.add(sql);
				}
			}
		}
		if (billNos != null) {
			for (String bill : billNos) {
				String sql = "UPDATE quyuHuizongShenhe SET isHz = 1 WHERE billNo = '" + bill + "'";
				sqls.add(sql);
			}
		}
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			log.error(e.getMessage());
			e.printStackTrace();
			return e.getMessage();
		}

		return result;

	}

	@SuppressWarnings("unchecked")
	public List<PsForm> searchPsFormByQuyuCode(String quyuCode, String startDate, String endDate, String rz) {
		String userCode = MainFrame.currentUser.getStaffCode();
		String orgs = "";
		if (quyuCode == null) {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUser(userCode);
		} else {
			orgs = MgrFactory.getInstance().createOrgMgr().getOrgCodeByUserQuyu(userCode, quyuCode);
		}

		String sql = "SELECT * FROM PsForm WHERE shopCode IN (" + orgs + ") AND cast(PsDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'"
				+ startDate + "'),120) AND CAST(PsDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + endDate + "'),120)";
		if (rz.equals("已入账")) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals("未入账")) {
			sql = sql + " AND rzDate = ''";
		}
		List<?> list = super.query(sql);
		return (List<PsForm>) list;
	}

	@SuppressWarnings("unchecked")
	public List<PsForm> searchPsForm(String orgCode, String startDate, String endDate, String rz) {
		String sql = "SELECT * FROM PsForm WHERE shopCode = ? AND cast(PsDate as char(10)) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120) AND CAST(PsDate as char(10)) <= CONVERT(VARCHAR(10),CONVERT(DATETIME,?),120)";
		if (rz.equals("已入账")) {
			sql = sql + " AND rzDate <> ''";
		} else if (rz.equals("未入账")) {
			sql = sql + " AND rzDate = ''";
		}
		Object[] args = new Object[] { orgCode, startDate, endDate };
		List<?> list = super.query(sql, args);
		return (List<PsForm>) list;
	}

	public PsForm getFormByBillNo(String billNo) {
		String sql = "SELECT * FROM PsForm WHERE psBillNo = ?";
		Object[] args = new Object[] { billNo };
		List<?> list = super.query(sql, args);
		if (list != null && (!list.isEmpty())) {
			return (PsForm) list.get(0);
		}
		return null;
	}

	public void rz(String bill) throws DatabaseException {
		CallDTO dto = new CallDTO();
		String call = "{CALL psrz(?,?)}";
		dto.setBill(bill);
		dto.setCall(call);
		dto.setHxdbName(ConnectionPool.hxDbName);
		super.callProcedure(dto);
	}

	public String deletePeisong(String billNo) {
		String result = "";
		List<String> sqls = new ArrayList<String>();

		String sql = "DELETE FROM psForm WHERE PsBillNo = '" + billNo + "'";
		sqls.add(sql);
		sql = "DELETE FROM psDetail WHERE PsBillNo = '" + billNo + "'";
		sqls.add(sql);
		try {
			executeBatch(sqls);
		} catch (DatabaseException e) {
			e.printStackTrace();
			log.error(e.getMessage());
			return e.getMessage();
		}
		return result;
	}
}
